﻿using Sustainalytics.ClientDW.Entities.Universes;
using Sustainalytics.DynamicsCRMGateway.Entities;
using Sustainalytics.Entities.ESGRatings;
using System;
using System.Collections.Generic;
using System.Linq;
using AccountFeatureEnum = Sustainalytics.Auth.Claims.AccountFeature;

namespace Sustainalytics.DynamicsCRMGateway
{
    public class UniversesCrmReader
    {
        private readonly CrmServiceContext _crmServiceContext;

        public UniversesCrmReader(CrmServiceContext crmServiceContext)
        {
            _crmServiceContext = crmServiceContext;
        }

        public IEnumerable<Universe> GetUniverses(int nextUniverseId)
        {
            // sort by AccountName then by ProductUniverseName in order to get consistent ids for universes
            return AccountWithProductUniverseQuery()
                .Select(x => new
                {
                    AccountId = x.Account.Id,
                    AccountName = x.Account.Name,
                    UniverseName = x.ProductUniverse.Name
                })
                .Distinct()
                .ToArray() // we need to sort in memory because the CRM can't order by related entities attributes
                .OrderBy(x => x.AccountName)
                .ThenBy(x => x.UniverseName)
                .Select((x, index) => new Universe()
                {
                    Id = nextUniverseId + index,
                    AccountId = x.AccountId,
                    Name = x.UniverseName
                }
            ).ToArray();
        }

        public IEnumerable<FeatureUniverse> GetFeatureUniverses(IEnumerable<Universe> universes)
        {
            // x.ProductUniverse.Id is equal to Guid.Empty; using instead x.AccountUniverse.ProductUniverseRef.Id
            var query = (
                from x in AccountWithProductUniverseQuery()
                join bridge in _crmServiceContext.UniverseFeatureProductUniverseBridgeSet on x.AccountUniverse.ProductUniverseRef.Id equals bridge.ProductUniverseId
                join universeFeature in _crmServiceContext.UniverseFeatureSet on bridge.UniverseFeatureId equals universeFeature.Id
                select new
                {
                    AccountId = x.Account.Id,
                    UniverseName = x.ProductUniverse.Name,
                    FeatureName = universeFeature.Name
                }
            ).Distinct().ToArray();

            return (
                from x in query
                join universe in universes on
                    new { AccountId = x.AccountId, UniverseName = x.UniverseName } equals
                    new { AccountId = universe.AccountId, UniverseName = universe.Name }
                select new
                {
                    AccountId = x.AccountId,
                    FeatureName = x.FeatureName,
                    UniverseId = universe.Id
                })
                .GroupBy(x => new { x.AccountId, x.FeatureName })
                .Select(grp => new FeatureUniverse()
                {
                    AccountId = grp.Key.AccountId,
                    Feature = (AccountFeatureEnum)Enum.Parse(typeof(AccountFeatureEnum), grp.Key.FeatureName),
                    UniverseIds = grp.Select(x => x.UniverseId).OrderBy(x => x).ToList()
                }
            ).ToArray();
        }

        public IEnumerable<UniverseCompany> GetStandardUniverseCompanies(IEnumerable<Universe> universes, IEnumerable<ReferenceUniverse> referenceUniverses)
        {
            var query = (
                from x in AccountWithProductUniverseQuery()
                join bridge in _crmServiceContext.CompaniesGroupAccountUniverseBridgeSet on x.AccountUniverse.AccountUniverseId equals bridge.AccountUniverseId
                join companiesGroup in _crmServiceContext.CompaniesGroupSet on bridge.CompaniesGroupId equals companiesGroup.Id
                where companiesGroup.IsStandard != null && companiesGroup.IsStandard.Value == true
                select new
                {
                    AccountId = x.Account.Id,
                    UniverseName = x.ProductUniverse.Name,
                    CompaniesGroupName = companiesGroup.Name
                }
            ).Distinct().ToArray();

            return (
                from x in query
                join universe in universes on
                    new { AccountId = x.AccountId, UniverseName = x.UniverseName } equals
                    new { AccountId = universe.AccountId, UniverseName = universe.Name }
                join referenceUniverse in referenceUniverses on x.CompaniesGroupName equals referenceUniverse.Name
                from company in referenceUniverse.Companies
                select new UniverseCompany()
                {
                    CompanyId = company.CompanyId,
                    AccountId = x.AccountId,
                    CIQID = company.CapitalIqId.ToString(),
                    UniverseId = universe.Id
                }
            ).DistinctBy(x => new { x.CompanyId, x.AccountId, x.UniverseId }).ToArray();
        }

        public IEnumerable<UniverseCompany> GetCustomUniverseCompanies(IEnumerable<Universe> universes)
        {
            var query = (
                from x in AccountWithProductUniverseQuery()
                join bridge in _crmServiceContext.CompaniesGroupAccountUniverseBridgeSet on x.AccountUniverse.AccountUniverseId equals bridge.AccountUniverseId
                join companiesGroup in _crmServiceContext.CompaniesGroupSet on bridge.CompaniesGroupId equals companiesGroup.Id
                join companiesGroupConstituent in _crmServiceContext.CompaniesGroupConstituentSet on companiesGroup.Id equals companiesGroupConstituent.CompaniesGroupRef.Id
                join company in _crmServiceContext.CompanySet on companiesGroupConstituent.CompanyRef.Id equals company.CompanyId
                where companiesGroup.IsStandard == null || companiesGroup.IsStandard.Value == false
                select new
                {
                    CompanyId = company.CompanyId.Value,
                    AccountId = x.Account.Id,
                    CIQID = company.CapitalIqId,
                    UniverseName = x.ProductUniverse.Name
                }
            ).Distinct().ToArray();

            return (
                from x in query
                join universe in universes on
                    new { AccountId = x.AccountId, UniverseName = x.UniverseName } equals
                    new { AccountId = universe.AccountId, UniverseName = universe.Name }
                select new UniverseCompany()
                {
                    CompanyId = x.CompanyId,
                    AccountId = x.AccountId,
                    CIQID = x.CIQID,
                    UniverseId = universe.Id
                }
            ).ToArray();
        }

        private IEnumerable<AccountWithProductUniverse> AccountWithProductUniverseQuery()
        {
            return (
                from account in _crmServiceContext.AccountSet
                join accountUniverse in _crmServiceContext.AccountUniverseSet on account.Id equals accountUniverse.AccountRef.Id
                join productUniverse in _crmServiceContext.ProductUniverseSet on accountUniverse.ProductUniverseRef.Id equals productUniverse.Id
                where account.RelationshipType == (int?)AccountRelationshipType.Client ||
                      account.RelationshipType == (int?)AccountRelationshipType.Prospect ||
                      account.RelationshipType == (int?)AccountRelationshipType.Demo
                select new AccountWithProductUniverse()
                {
                    Account = account,
                    AccountUniverse = accountUniverse,
                    ProductUniverse = productUniverse
                }
            ).ToArray();
        }

        public CustomObjectForLaura[] AccountWithCompaniesGroupQuery()
        {
            return (
                from account in _crmServiceContext.AccountSet
                join accountUniverse in _crmServiceContext.AccountUniverseSet on account.Id equals accountUniverse.AccountRef.Id
                join accountUniverseCompaniesGroupsBridge in _crmServiceContext.CompaniesGroupAccountUniverseBridgeSet on accountUniverse.AccountUniverseId equals accountUniverseCompaniesGroupsBridge.AccountUniverseId
                join companiesGroups in _crmServiceContext.CompaniesGroupSet on accountUniverseCompaniesGroupsBridge.CompaniesGroupId equals companiesGroups.CompaniesGroupId
                join productUniverse in _crmServiceContext.ProductUniverseSet on accountUniverse.ProductUniverseRef.Id equals productUniverse.Id
                where account.RelationshipType == (int?)AccountRelationshipType.Client ||
                      account.RelationshipType == (int?)AccountRelationshipType.Prospect ||
                      account.RelationshipType == (int?)AccountRelationshipType.Demo
                select new CustomObjectForLaura()
                {
                    Account = account.Name,
                    ProductUniverse = productUniverse.Name,
                    CompaniesGroup = companiesGroups.Name
                }
            ).ToArray();
        }

        public class CustomObjectForLaura
        {
            public string Account { get; set; }
            public string ProductUniverse { get; set; }
            public string CompaniesGroup { get; set; }
        }

        private class AccountWithProductUniverse
        {
            public Account Account { get; set; }
            public AccountUniverse AccountUniverse { get; set; }
            public ProductUniverse ProductUniverse { get; set; }
        }
    }
}
